﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

    public class SubjectBusiness
    {
        public static bool InsertSubject(Subject subject)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "insert into subject(Title, Lead, Description, Metakey, TotalLike, TotalView, TotalComment, TypeSubject, AuthorID, Status, CreateDate, PublishDate, Priority, ImagePath, Visible, IconSubject, OriginalSubject, LinkSubject,AdminID) values(N@title,N@lead,N@description,N@metakey,@totalLike,@totalView,@totalComment,@typeSubject,@authorID,@status,@createDate,@publishDate,@priority,@imagePath,@visible,@iconSubject,N@original,@linkSubject,@adminID)";
              
                SqlParameter[] pa = new SqlParameter[18];
                pa[0] = new SqlParameter("@title", subject.Title);
                pa[1] = new SqlParameter("@lead", subject.Lead);
                pa[2] = new SqlParameter("@description", subject.Description);
                pa[3] = new SqlParameter("@totalLike", subject.TotalLike);
                pa[4] = new SqlParameter("@totalView", subject.TotalView);
                pa[5] = new SqlParameter("@totalComment", subject.TotalComment);
                pa[6] = new SqlParameter("@typeSubject", subject.TypeSubject);
                pa[7] = new SqlParameter("@authorID", subject.AuthorID);
                pa[8] = new SqlParameter("@status", subject.Status);
                pa[9] = new SqlParameter("@createDate", subject.CreateDate);
                pa[10] = new SqlParameter("@publishDate", subject.PublishDate);
                pa[11] = new SqlParameter("@priority", subject.Priority);
                pa[12] = new SqlParameter("@imagePath", subject.ImagePath);
                pa[13] = new SqlParameter("@visible", subject.Visible);
                pa[14] = new SqlParameter("@iconSubject", subject.IconSubject);
                pa[15] = new SqlParameter("@original", subject.OriginalSubject);
                pa[16] = new SqlParameter("@linkSubject", subject.LinkSubject);
                pa[17] = new SqlParameter("@adminID",subject.AdminID);
                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateSubject(int id, Subject subject)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                bool rs = false;
                string sql = "update subject set Title=@title,Lead=@lead,Description=@description,TypeSubject=@typeSubject,ImagePath=@imagepath where ID=@subjectID";
                SqlParameter[] pa = new SqlParameter[6];
                pa[0] = new SqlParameter("@title", subject.Title);
                pa[1] = new SqlParameter("@lead", subject.Lead);
                pa[2] = new SqlParameter("@description", subject.Description);
                pa[3] = new SqlParameter("@typeSubject", subject.TypeSubject);
                pa[4] = new SqlParameter("@imagepath", subject.ImagePath);
                pa[5] = new SqlParameter("@subjectID", id);

                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubject(int start, int end)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY publishdate desc) AS rownum,* FROM subject) AS subjectpaging WHERE rownum >= @start AND rownum <= @end ORDER BY publishdate desc ";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@start", start);
                pa[1] = new SqlParameter("@end", end);
                SqlCommand command = db.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Description = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());

                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["Status"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());

                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubjectBuild(int start, int end)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                List<Subject> list = new List<Subject>();

                string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY sb.Priority desc) AS rownum,ad.UserName,s.ID,s.Title,s.IconSubject,s.ImagePath,s.Lead,s.AuthorID,s.Description,s.TotalLike,s.TotalView,s.TotalComment,s.TypeSubject,s.CreateDate,s.Status,s.Visible,sb.CriteriaID FROM subject as s,subjectbuild as sb,admin as ad where s.ID=sb.SubjectID and s.AuthorID=ad.AdminID) AS subjectbuildpaging WHERE  rownum >= @start AND rownum <= @end ";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@start", start);
                pa[1] = new SqlParameter("@end", end);
                SqlCommand command = db.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.CriteriaID = int.Parse(reader["CriteriaID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Description = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["Status"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static List<Subject> GetListAllSubjectVisible(int visible, int start, int end)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "SELECT *FROM (SELECT ROW_NUMBER() OVER(ORDER BY publishdate desc) AS rownum,ad.UserName,sub.ID,sub.Status,sub.IconSubject,sub.Title,sub.Lead,sub.Description,sub.TotalLike,sub.TotalView,sub.TotalComment,sub.TypeSubject,sub.AuthorID,sub.CreateDate,sub.Priority,sub.ImagePath,sub.OriginalSubject,sub.LinkSubject,sub.PublishDate,sub.Visible FROM subject as sub ,admin as ad where sub.AuthorID=ad.AdminID) AS subjectpaging WHERE  Visible=@visible and rownum >= @start AND rownum <= @end ORDER BY publishdate desc ";
                SqlParameter[] pa = new SqlParameter[3];
                pa[0] = new SqlParameter("@visible", visible);
                pa[1] = new SqlParameter("@start", start);
                pa[2] = new SqlParameter("@end", end);
                SqlCommand command = db.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Description = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["Status"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }


        public static List<Subject> GetListAllSubjectVideosVisible(int visible, int typeSubject, int start, int end)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                List<Subject> list = new List<Subject>();
                string sql = "SELECT *FROM (SELECT ROW_NUMBER() OVER(ORDER BY publishdate desc) AS rownum,ad.UserName,sub.ID,sub.Status,sub.IconSubject,sub.Title,sub.Lead,sub.Description,sub.TotalLike,sub.TotalView,sub.TotalComment,sub.TypeSubject,sub.AuthorID,sub.CreateDate,sub.Priority,sub.ImagePath,sub.OriginalSubject,sub.LinkSubject,sub.PublishDate,sub.Visible FROM subject as sub ,admin as ad where sub.AuthorID=ad.AdminID and typeSubject=@typeSubject) AS subjectpaging WHERE  Visible=@visible and rownum >= @start AND rownum <= @end ORDER BY publishdate desc ";
                SqlParameter[] pa = new SqlParameter[4];
                pa[0] = new SqlParameter("@visible", visible);
                pa[1] = new SqlParameter("@start", start);
                pa[2] = new SqlParameter("@end", end);
                pa[3] = new SqlParameter("@typeSubject", typeSubject);
                SqlCommand command = db.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Subject sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Description = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["Status"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorName = reader["UserName"].ToString();
                    list.Add(sb);
                }
                return list;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static Subject GetSubjectDetail(int subjectID)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                Subject sb = null;
                string sql = "select ad.UserName,*from subject as s,admin as ad where ID=@subjectID and s.AuthorID=ad.AdminID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@subjectID", subjectID);
                SqlCommand command = db.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    sb = new Subject();
                    sb.ID = int.Parse(reader["ID"].ToString());
                    sb.Title = reader["Title"].ToString();
                    sb.Lead = reader["Lead"].ToString();
                    sb.Description = reader["Description"].ToString();
                    sb.ImagePath = reader["ImagePath"].ToString();
                    sb.TypeSubject = int.Parse(reader["TypeSubject"].ToString());
                  
                    sb.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    sb.Status = int.Parse(reader["Status"].ToString());
                    sb.Visible = int.Parse(reader["Visible"].ToString());
                    sb.TotalLike = int.Parse(reader["TotalLike"].ToString());
                    sb.TotalView = int.Parse(reader["TotalView"].ToString());
                    sb.TotalComment = int.Parse(reader["TotalComment"].ToString());
                    sb.IconSubject = reader["IconSubject"].ToString();
                    sb.LinkSubject = reader["LinkSubject"].ToString();
                    sb.AuthorID = int.Parse(reader["AuthorID"].ToString());
                    sb.AuthorName = reader["UserName"].ToString();
                }
                return sb;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateViewSubject(int subjectID)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                string sql = "update subject set TotalView=TotalView+1 where ID=@id";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@id", subjectID);
                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateCommentSubject(int subjectID)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                string sql = "update subject set TotalComment=TotalComment+1 where ID=@id";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@id", subjectID);
                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool UpdateLikeSubject(int subjectID)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                string sql = "update subject set TotalLike=TotalLike+1 where ID=@id";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@id", subjectID);
                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool InsertSubjectBuild(SubjectBuild build)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                string sql = "insert into subjectbuild(CriteriaID, SubjectID, CreateDate, Priority, Status) values(@criterialID,@subjectID,@createDate,@priority,@status)";
                SqlParameter[] pa = new SqlParameter[5];
                pa[0] = new SqlParameter("@criterialID", build.CriteriaID);
                pa[1] = new SqlParameter("@subjectID", build.SubjectID);
                pa[2] = new SqlParameter("@createDate", build.CreateDate);
                pa[3] = new SqlParameter("@priority", build.Priority);
                pa[4] = new SqlParameter("@status", build.Status);
                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }

        public static bool RemoveSubjectBuild(int criterialID, int subjectID)
        {
            ClassDatabase db = new ClassDatabase();
            try
            {
                string sql = "delete subjectbuild where CriteriaID=@criterialID and SubjectID=@subjectID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@criterialID", criterialID);
                pa[1] = new SqlParameter("@subjectID", subjectID);
                SqlCommand command = db.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                bool rs = false;
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                db.CloseData();
            }
        }
    }

